﻿using UnityEngine;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections.Generic;
using System.Linq;
using System;
using System.Text;
using NPOI.HSSF.UserModel;
using System.Threading.Tasks;
using System.Collections;

namespace ExcelReader
{
    public class ExcelReaderService : MonoBehaviour, System.IDisposable
    {
        private FileStream fs;
        private XSSFWorkbook workbook;

        public static ExcelReaderService CreateExcelReader(string filePath)
        {
            try
            {
                if (File.Exists(filePath))
                {
                    GameObject obj = new GameObject();
                    obj.name = "[ExcelReaderService]";
                    var reader = obj.AddComponent<ExcelReaderService>();

                    reader.fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                    reader.workbook = new XSSFWorkbook(reader.fs);
                    return reader;
                }
                else
                {
                    return null;
                }
            }
            catch (IOException ex)
            {
                Debug.LogError("Error opening Excel file: " + ex.Message);
                return null;
            }
        }

        public void Dispose()
        {
            if (workbook != null)
            {
                workbook.Close();
            }
            if (fs != null)
            {
                fs.Close();
                fs.Dispose();
            }
        }

        private void OnDestroy()
        {
            Dispose();
        }

        private void OnApplicationQuit()
        {
            Dispose();
        }

        public ExcelSheet GetSheet(string sheetName)
        {
            if (workbook == null)
            {
                Debug.LogError("Workbook is not initialized.");
                return null;
            }
            return new ExcelSheet(workbook, workbook.GetSheet(sheetName) as XSSFSheet, this);
        }

        public ExcelSheet GetSheet(int index)
        {
            if (workbook == null)
            {
                Debug.LogError("Workbook is not initialized.");
                return null;
            }
            return new ExcelSheet(workbook, workbook.GetSheetAt(index) as XSSFSheet, this);
        }

        public class ExcelSheet
        {
            private XSSFSheet sheet;
            private XSSFWorkbook workbook;
            public int StartRowsIndex => sheet.FirstRowNum;
            public int EndRowIndex => sheet.LastRowNum;

            private MonoBehaviour host;

            public ExcelSheet(XSSFWorkbook workbook, XSSFSheet sheet, MonoBehaviour mono)
            {
                this.sheet = sheet;
                this.workbook = workbook;
                this.host = mono;
            }

            public List<string> ReadPlainTextFromRow(int row, int startColumn = 0, int endColumn = int.MaxValue)
            {
                (startColumn, endColumn) = ValidateColumnRange(startColumn, endColumn);
                List<string> texts = new List<string>();
                for (int i = startColumn; i <= endColumn; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(i);
                    texts.Add(cell != null ? GetPlainText(cell) : "");
                }
                return texts;
            }

            public List<string> ReadRichTextFromRow(int row, int startColumn = 0, int endColumn = int.MaxValue)
            {
                (startColumn, endColumn) = ValidateColumnRange(startColumn, endColumn);
                List<string> texts = new List<string>();
                for (int i = startColumn; i <= endColumn; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(i);
                    texts.Add(cell != null ? GetRichText(cell) : "");
                }
                return texts;
            }

            public List<float> ReadNumbersFromRow(int row, int startColumn = 0, int endColumn = int.MaxValue)
            {
                (startColumn, endColumn) = ValidateColumnRange(startColumn, endColumn);
                List<float> numbers = new List<float>();
                for (int i = startColumn; i <= endColumn; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(i);
                    float number = cell != null && cell.CellType == CellType.Numeric ? (float)cell.NumericCellValue : float.NaN;
                    numbers.Add(number);
                }
                return numbers;
            }

            public List<string> ReadPlainTextFromColumn(int column, int startRow = 0, int endRow = int.MaxValue)
            {
                (startRow, endRow) = ValidateRowRange(startRow, endRow);
                List<string> texts = new List<string>();
                for (int i = startRow; i <= endRow; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(i)?.GetCell(column);
                    texts.Add(cell != null ? GetPlainText(cell) : "");
                }
                return texts;
            }

            public List<string> ReadRichTextFromColumn(int column, int startRow = 0, int endRow = int.MaxValue)
            {
                (startRow, endRow) = ValidateRowRange(startRow, endRow);
                List<string> texts = new List<string>();
                for (int i = startRow; i <= endRow; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(i)?.GetCell(column);
                    texts.Add(cell != null ? GetRichText(cell) : "");
                }
                return texts;
            }

            public List<float> ReadNumbersFromColumn(int column, int startRow = 0, int endRow = int.MaxValue)
            {
                (startRow, endRow) = ValidateRowRange(startRow, endRow);
                List<float> numbers = new List<float>();
                for (int i = startRow; i <= endRow; i++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(i)?.GetCell(column);
                    float number = cell != null && cell.CellType == CellType.Numeric ? (float)cell.NumericCellValue : float.NaN;
                    numbers.Add(number);
                }
                return numbers;
            }

            public string ReadPlainTextFromCell(int row, int column)
            {
                XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(column);
                return cell != null ? GetPlainText(cell) : "";
            }

            public string ReadRichTextFromCell(int row, int column)
            {
                XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(column);
                return cell != null ? GetRichText(cell) : "";
            }

            public float ReadNumberFromCell(int row, int column)
            {
                XSSFCell cell = (XSSFCell)sheet.GetRow(row)?.GetCell(column);
                return cell != null && cell.CellType == CellType.Numeric ? (float)cell.NumericCellValue : float.NaN;
            }

            private string GetPlainText(XSSFCell cell)
            {
                if (cell.CellType == CellType.String)
                {
                    return cell.StringCellValue;
                }
                else if (cell.CellType == CellType.Numeric)
                {
                    return cell.NumericCellValue.ToString();
                }
                else if (cell.CellType == CellType.Boolean)
                {
                    return cell.BooleanCellValue.ToString();
                }
                return string.Empty;
            }

            public string GetRichText(XSSFCell cell)
            {
                if (cell != null)
                {
                    if (cell.CellType == CellType.String)
                    {
                        ICellStyle cellStyle = cell.CellStyle;
                        if (cellStyle != null)
                        {

                            XSSFFont font = (XSSFFont)workbook.GetFontAt(cellStyle.FontIndex);
                            if (!string.IsNullOrEmpty(cell.StringCellValue))
                            {
                                // Check for cell-level formatting
                                string formattedText = GetFormattedText(cell.StringCellValue, font);
                                return formattedText;
                            }
                            else
                            {
                                return string.Empty;
                            }
                        }
                        else
                        {
                            StringBuilder richTextBuilder = new StringBuilder();
                            XSSFRichTextString richTextString = (XSSFRichTextString)cell.RichStringCellValue;

                            if (richTextString.NumFormattingRuns > 0)
                            {
                                // Iterate over formatting runs in the rich text string
                                for (int i = 0; i < richTextString.NumFormattingRuns; i++)
                                {
                                    int startIndex = richTextString.GetIndexOfFormattingRun(i);
                                    int endIndex = i == richTextString.NumFormattingRuns - 1 ? richTextString.Length : richTextString.GetIndexOfFormattingRun(i + 1);
                                    IFont font = richTextString.GetFontAtIndex(startIndex);

                                    // Get the text for this formatting run
                                    string runText = richTextString.String.Substring(startIndex, endIndex - startIndex);

                                    // Append formatted text to the builder
                                    richTextBuilder.Append(GetFormattedText(runText, font));
                                }
                                return richTextBuilder.ToString();
                            }
                            else
                            {
                                return cell.StringCellValue;
                            }
                        }
                    }
                    else if (cell.CellType == CellType.Numeric)
                    {
                        return cell.NumericCellValue.ToString();
                    }
                    else if (cell.CellType == CellType.Boolean)
                    {
                        return cell.BooleanCellValue.ToString();
                    }
                }
                return string.Empty;
            }

            // Helper method to apply formatting to text
            private string GetFormattedText(string text, IFont font)
            {
                if (font == null)
                {
                    return text;
                }

                StringBuilder formattedTextBuilder = new StringBuilder();

                // Check for bold
                if (font.IsBold)
                {
                    formattedTextBuilder.Append("<b>");
                }

                // Check for italic
                if (font.IsItalic)
                {
                    formattedTextBuilder.Append("<i>");
                }

                // Get color from workbook palette based on color index
                string colorHex = GetFontColor(font);

                // Append text with color if available
                if (!string.IsNullOrEmpty(colorHex))
                {
                    // Append text with color
                    formattedTextBuilder.Append($"<color={colorHex}>");
                }

                // Append text
                formattedTextBuilder.Append(text);

                // Close color tag if color was applied
                if (!string.IsNullOrEmpty(colorHex))
                {
                    formattedTextBuilder.Append("</color>");
                }

                // Close italic tag if necessary
                if (font.IsItalic)
                {
                    formattedTextBuilder.Append("</i>");
                }

                // Close bold tag if necessary
                if (font.IsBold)
                {
                    formattedTextBuilder.Append("</b>");
                }

                return formattedTextBuilder.ToString();
            }

            private string GetFontColor(IFont font)
            {
                //var theme = (sheet.Workbook as XSSFWorkbook)?.GetTheme();
                //if (theme != null && font is XSSFFont xssfFont)
                //{
                //    var themeColorIndex = xssfFont.GetThemeColor();
                //    var themeColor = theme.GetThemeColor(themeColorIndex);
                //    if (themeColor != null)
                //    {
                //        string hexColor = string.Format("#{0:X2}{1:X2}{2:X2}", themeColor.RGB[0], themeColor.RGB[1], themeColor.RGB[2]);
                //        return hexColor;
                //    }
                //}

                // If theme color is not available or not applicable, get the color from the font directly
                var color = (font as XSSFFont)?.GetXSSFColor();
                if (color != null && color.ARGBHex.Substring(2, 6) != "000000")
                {
                    string hexColor = string.Format("#{0:X2}{1:X2}{2:X2}", color.RGB[0], color.RGB[1], color.RGB[2]);
                    return hexColor;
                }

                // Default to empty string if color is not found
                return "#ffffff";
            }

            private (int, int) ValidateColumnRange(int startColumn, int endColumn)
            {
                startColumn = Math.Max(startColumn, 0);
                endColumn = Math.Min(endColumn, sheet.GetRow(0).LastCellNum);
                endColumn = Math.Max(endColumn, startColumn);
                return (startColumn, endColumn);
            }

            private (int, int) ValidateRowRange(int startRow, int endRow)
            {
                startRow = Math.Max(startRow, 0);
                endRow = Math.Min(endRow, sheet.LastRowNum);
                endRow = Math.Max(endRow, startRow);
                return (startRow, endRow);
            }

            public async Task<List<Texture2D>> ReadImagesOfRows(int startRow, int endRow)
            {
                return await ReadImagesOfRange(startRow, endRow, 0, int.MaxValue);
            }

            public async Task<List<Texture2D>> ReadImagesOfColumn(int startColumn, int endColumn)
            {
                return await ReadImagesOfRange(0, int.MaxValue, startColumn, endColumn);
            }

            public List<XSSFPicture> GetImagesReferenceOfRows(int startRow, int endRow)
            {
                return GetImagesReferenceOfRange(startRow, endRow, 0, int.MaxValue);
            }

            public List<XSSFPicture> GetImagesReferenceOfColumn(int startColumn, int endColumn)
            {
                return GetImagesReferenceOfRange(0, int.MaxValue, startColumn, endColumn);
            }

            public async Task<List<Texture2D>> ReadImagesOfRange(int startRow, int endRow, int startColumn, int endColumn)
            {
                if (startColumn > endColumn || startRow > endRow)
                {
                    return null;
                }

                TaskCompletionSource<List<byte[]>> tsc = new TaskCompletionSource<List<byte[]>>();
                await Task.Run(() =>
                {
                    List<byte[]> imagesBytes = new List<byte[]>();
                    var references = GetImagesReferenceOfRange(startRow, endRow, startColumn, endColumn);
                    foreach (var pictureRef in references)
                    {
                        imagesBytes.Add(pictureRef.PictureData.Data);
                    }
                    tsc.SetResult(imagesBytes);
                });
                var bytesArray = await tsc.Task;
                var images = new List<Texture2D>();
                foreach (var bytes in bytesArray)
                {
                    images.Add(ExcelSheetExt.ByteToTexture2D(bytes));
                    await AwaitNextFrame();
                }
                return images;
            }

            public List<XSSFPicture> GetImagesReferenceOfRange(int startRow, int endRow, int startColumn, int endColumn)
            {
                List<XSSFPicture> refs = new List<XSSFPicture>();
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                List<XSSFShape> shapes = drawing.GetShapes();
                foreach (XSSFShape shape in shapes)
                {
                    if (shape is XSSFPicture)
                    {
                        XSSFPicture picture = (XSSFPicture)shape;
                        XSSFClientAnchor anchor = (XSSFClientAnchor)picture.GetAnchor();
                        if (anchor.Col1 >= startColumn && anchor.Col2 <= endColumn && anchor.Row1 >= startRow && anchor.Row2 <= endRow)
                        {
                            refs.Add(picture);
                        }
                    }
                }
                return refs;
            }


            private async Task AwaitNextFrame()
            {
                // �ڲ�����
                IEnumerator DelayOneFrameCoroutine(Action onComplete)
                {
                    yield return new WaitForEndOfFrame();
                    onComplete?.Invoke();
                }

                var tcs = new TaskCompletionSource<bool>();
                host.StartCoroutine(DelayOneFrameCoroutine(() => tcs.SetResult(true)));
                await tcs.Task;
            }
        }
    }
}

public class ContentAndRange<T>
{
    public int startInclude;
    public int endExclude;
    public int endInclude => endExclude - 1;
    public T content;
}

public static class ExcelSheetExt
{
    public static List<ContentAndRange<string>> GetDistinctTexts(this List<string> texts, int offset)
    {
        List<ContentAndRange<string>> result = new List<ContentAndRange<string>>();
        if (texts?.Count > 0)
        {
            string prevStr = string.Empty;
            for (int i = 0; i < texts.Count; i++)
            {
                if (string.IsNullOrEmpty(texts[i]))
                {
                    continue;
                }
                if (prevStr == string.Empty || texts[i] != prevStr)
                {
                    if (i > 0)
                    {
                        var lastOne = result.LastOrDefault();
                        if (lastOne != null)
                        {
                            lastOne.endExclude = i + offset;
                        }
                    }

                    prevStr = texts[i];
                    result.Add(new ContentAndRange<string>
                    {
                        startInclude = i + offset,
                        endExclude = texts.Count + offset,
                        content = prevStr
                    });

                }
            }
        }
        return result;
    }

    public static string ToHex(this System.Drawing.Color drawingColor)
    {
        return "#" + drawingColor.R.ToString("X2") + drawingColor.G.ToString("X2") + drawingColor.B.ToString("X2") + drawingColor.A.ToString("X2");
    }

    public static Texture2D ByteToTexture2D(byte[] imageData)
    {
        Texture2D texture = new Texture2D(2, 2);
        if (imageData != null)
        {
            texture.LoadImage(imageData); // Load the image data into the texture
        }
        return texture;
    }

    /// <summary>
    /// ���ÿ���
    /// </summary>
    /// <param name="source">ԭͼ</param>
    /// <param name="targetWidth">Ŀ�����</param>
    /// <param name="targetHeight">Ŀ��߶�</param>
    /// <param name="widthOrHeightRatio">1 width, 0 height</param>
    /// <returns></returns>
    public static Texture2D AdaptTexture(this Texture2D source, int targetWidth, int targetHeight, float widthOrHeightRatio)
    {
        widthOrHeightRatio = Mathf.Clamp01(widthOrHeightRatio);
        float sourceAspect = (float)source.width / source.height;
        int newWidth, newHeight;

        if (widthOrHeightRatio == 0f)
        {
            newHeight = targetHeight;
            newWidth = Mathf.RoundToInt(targetHeight * sourceAspect);
        }
        else if (widthOrHeightRatio == 1f)
        {
            newWidth = targetWidth;
            newHeight = Mathf.RoundToInt(targetWidth / sourceAspect);
        }
        else
        {
            float aspectRatio = Mathf.Lerp(1 / sourceAspect, sourceAspect, widthOrHeightRatio);
            newWidth = Mathf.RoundToInt(targetWidth * aspectRatio);
            newHeight = Mathf.RoundToInt(targetHeight / aspectRatio);
        }

        // Create a new texture with the calculated dimensions
        Texture2D resizedTexture = new Texture2D(newWidth, newHeight);

        // Calculate the aspect ratio scaling
        float aspectX = (float)source.width / newWidth;
        float aspectY = (float)source.height / newHeight;

        // Scale and copy the pixels from the source texture to the resized texture
        Color[] pixels = new Color[newWidth * newHeight];
        for (int y = 0; y < newHeight; y++)
        {
            for (int x = 0; x < newWidth; x++)
            {
                int sourceX = Mathf.Clamp(Mathf.RoundToInt(x * aspectX), 0, source.width - 1);
                int sourceY = Mathf.Clamp(Mathf.RoundToInt(y * aspectY), 0, source.height - 1);
                pixels[y * newWidth + x] = source.GetPixel(sourceX, sourceY);
            }
        }
        resizedTexture.SetPixels(pixels);
        resizedTexture.Apply();

        return resizedTexture;
    }
}
